import functools

from flask import Blueprint, render_template, request, redirect, url_for, flash, make_response, session
from function.sqlite_function import *
import os
import functools

# 本蓝图用于加减零件数量
_select = Blueprint('select', __name__, url_prefix='/')


# 基本页面,只返回大类，小类随时查
@_select.route('/select/index')
def select_index():
    con, cur = db_open()
    data = sqliteObject_to_list_h(cur, f'''
        select distinct bigname, round(sum(total),2) 
        from stateless_part
        group by bigname
    ''')
    total_price = 0
    for i in data:
        total_price = total_price + i[1]
    total_price = round(total_price,2)
    # 小类搜索框中的预设文字
    temp1 = [['请输入', '']]
    if session.get('identity') == '用户':
        return render_template('select/select.html', data=data, temp1=temp1, total_price=total_price)
    else:
        return render_template('select/select_visitor.html', data=data, temp1=temp1, total_price=total_price)


# 返回小类
@_select.route('/select1', methods=['POST'])
def return_smallname():
    a = request.form.get('a')
    con, cur = db_open()
    data = sqliteObject_to_list_h(cur, f'''
        select smallname, unitprice, number, round(total,2)
        from stateless_part 
        where bigname = '{a}'
    ''')
    total_price = 0
    for i in data:
        total_price = total_price + i[3]
    total_price = round(total_price, 2)
    return render_template('select/smallname_table.html', data=data, bigname=a, total_price=total_price)


# 返回小类详细信息
@_select.route('/select2', methods=['POST'])
def return_smallname_details():
    bigname = request.form.get('bigname')
    smallname = request.form.get('smallname')

    con, cur = db_open()

    # 校验大类和小类是否存在
    bigname_check = sqliteObject_to_list_n(cur, f'''
            select name from bigname where name = '{bigname}'
        ''')
    if bigname_check == '':
        return '1'
    smallname_check = sqliteObject_to_list_n(cur, f'''
            select smallname from stateless_part where bigname = '{bigname}'
        ''')
    if smallname_check == '':
        return '2'

    # 按照日期顺序返回小类全部记录
    data = sqliteObject_to_list_h(cur, f'''
        select date, bigname, smallname, handle, change, unitprice, round(price,2), rowid 
        from '{bigname}' 
        where smallname = '{smallname}' 
        order by date 
    ''')

    # 获取最新记录的单价和行号用于自动填写和修改时对比是否日期改变
    if data == []:
        unitprice = ''
        rowid = ''
    else:
        unitprice = data[-1][5]
        rowid = data[-1][7]

    # 返回这种零件目前的在库数量作为参考
    num = sqliteObject_to_list_a(cur, f'''
        select unitprice,number,total,bigname,smallname from stateless_part where bigname = "{bigname}" and smallname = "{smallname}"
    ''')

    return render_template('select/smallname_details_table.html', data=data, bigname=bigname, smallname=smallname, unitprice=unitprice, rowid=rowid, num=num)


# 从小类返回大类
@_select.route('/select3', methods=['POST'])
def small_to_big():
    con, cur = db_open()
    data = sqliteObject_to_list_h(cur, f'''
        select distinct bigname, round(sum(total),2)
        from stateless_part
        group by bigname
    ''')
    return render_template('select/bigname_table.html', data=data)


# 零件入库
@_select.route('/select4', methods=['POST'])
def add():
    date = request.form.get('a')
    bigname = request.form.get('b')
    smallname = request.form.get('c')
    handle = request.form.get('d')
    change = int(request.form.get('e'))
    unitprice = int(request.form.get('f'))

    # 计算订单总价
    price = change * unitprice

    # 检验要添加的小项是否存在
    temp1 = exist(bigname, smallname)
    if temp1 == []:
        # 要添加的大类或者小类不存在
        return '1'

    # 计算单条记录总价和目前在库的零件的总价,并将目前单价，目前数量，目前总价值更新到汇总表中
    number = temp1[1] + change
    total = number * unitprice
    update_add(bigname, smallname, unitprice, number, total)

    # 向对应大类表中添加修改记录
    update_bigname(date, bigname, smallname, handle, change, unitprice, price)

    # 添加成功
    return '2'


# 零件被领用
@_select.route('/select5', methods=['POST'])
def reduce():
    date = request.form.get('a')
    bigname = request.form.get('b')
    smallname = request.form.get('c')
    handle = request.form.get('d')
    change = int(request.form.get('e'))

    temp1 = exist(bigname, smallname)
    if temp1 == []:
        # 要添加的大类或者小类不存在
        return '1'
    if change > temp1[1]:
        # 领用数量超过仓库目前现有库存
        return '2'

    # 单价是从总库中取出来的, 然后乘以数量算出总价
    unitprice = temp1[0]
    change = 0 - change
    price = temp1[0] * change

    # 计算单条记录总价和目前在库的零件的总价,并将目前单价，目前数量，目前总价值更新到汇总表中
    number = temp1[1] + change
    total = number * temp1[0]
    update_reduce(bigname, smallname, number, total)

    # 向对应大类表中添加修改记录
    update_bigname(date, bigname, smallname, handle, change, unitprice, price)

    # 领用成功
    return '3'


# 删掉加法，增加加法
# 删掉加法，增加减法
# 删掉减法，增加加法 自从自己之后的要更新
# 删掉减法，增加减法 更新自己
# 修改记录
@_select.route('/select6', methods=['POST'])
def modify():
    con, cur = db_open()

    date = request.form.get('a')
    bigname = request.form.get('b')
    smallname = request.form.get('c')
    handle = request.form.get('d')
    change = float(request.form.get('e'))
    unitprice = float(request.form.get('f'))
    rowid = int(request.form.get('g'))

    # 假定删除原始记录并添加新纪录后，检查是否会出现为0的情况
    temp10 = check_change_not_zero(date, bigname, smallname, change, rowid)
    if temp10 == False:
        # 修改后的记录会导致库存数量某一时刻为零
        return '1'
    if change < 0:
        price = 0
    else:
        price = change * unitprice
    # 如果这条记录为添加记录，从头到尾检查表格数量的合理性
    # 确认不为0，执行更新
    sql1 = f'''
        UPDATE '{bigname}'
        SET date = '{date}', bigname = '{bigname}', smallname = '{smallname}', handle = '{handle}', change = '{change}', unitprice = '{unitprice}', price = '{price}'
        WHERE  rowid = {rowid}
    '''
    cur.execute(sql1)
    con.commit()

    # 更新总表
    update_bigname_and_stateless_part(bigname, smallname)
    # 修改成功
    return '2'

# 这里有大问题
# 删除记录
@_select.route('/select7', methods=['POST'])
def delete():
    rowid = int(request.form.get('a').strip())
    bigname = request.form.get('b').strip()
    smallname = request.form.get('c').strip()

    con, cur = db_open()

    # 检查删除该行后表中零件是否会出现某时为负数的情况

    temp1 = check_delete_not_zero(bigname, smallname, rowid)

    # 如果删完这条记录后就是个空表了
    if temp1 == 3:
        # 如果表里就这一行数据的话，直接删掉即可
        cur.execute(f'''
            delete from '{bigname}' where rowid = '{rowid}'
        ''')
        # 更新总表
        sql2 = f'''
            UPDATE stateless_part
            SET unitprice = 0, number = 0, total = 0
            WHERE  bigname = '{bigname}' and smallname = '{smallname}'
        '''
        cur.execute(sql2)
        con.commit()
        # 删除成功
        return '1'

    # 如果删完记录出现平衡问题
    if temp1 == 1:
        # 修改后的记录会导致库存数量某一时刻为零
        return '2'

    # 排除所有异常后进行删除

    # 执行删除
    cur.execute(f'''
        delete from '{bigname}' where rowid = '{rowid}'
    ''')
    con.commit()

    # 更新总表
    update_bigname_and_stateless_part(bigname, smallname)
    # 删除成功
    return '1'


# 返回小类预测数据
@_select.route('/select8', methods=['POST'])
def smallname_forecast():
    con, cur = db_open()
    name = request.form.get('a')

    # 这里有问题，但是不知道发生了什么问题
    name = '%' + functools.reduce(lambda x, y: x + '%' + y, name) + '%'

    temp1 = sqliteObject_to_list_h(cur, f'''
        select bigname, smallname from stateless_part where smallname like '{name}'
    ''')

    return render_template('select/smallname_forecast.html', temp1=temp1)












# 检查删除原数据并添加新数据后是否会出现某时刻数量为零的问题（不用考虑表里删除原数据后为空的情况，因为总会插入一条）
def check_change_not_zero(date, bigname, smallname, change, rowid):
    # 返回去掉原数据的完整记录
    con, cur = db_open()
    temp1 = sqliteObject_to_list_h(cur, f'''
        select date, change
        from '{bigname}' 
        where smallname = '{smallname}' and ROWID != '{rowid}' 
        order by date
    ''')

    # 将修改后的记录插入表中
    temp2 = False
    for j in range(len(temp1)):
        if date < temp1[j][0]:
            temp1.insert(j, [date, change])
            temp2 = True
            break
    if temp2 == False:
        temp1.append([date, change])

    # 检查是否出现某时刻数量小于零的情况
    num = 0
    for i in temp1:
        num = num + i[1]
        if num < 0:
            return False
    return True


# 给定一个大类表，从第一条开始更新整个表格，随后更新总表(使用该函数前保证大类表中有内容)
def update_bigname_and_stateless_part(bigname, smallname):
    con, cur = db_open()
    temp1 = sqliteObject_to_list_h(cur, f'''
        select change, unitprice, rowid
        from '{bigname}' 
        where smallname = '{smallname}'
        order by date
    ''')

    now_unitprice = 0

    # 逐个对表中内容进行更新
    for i in temp1:
        if i[0] > 0:
            now_unitprice = i[1]
        else:
            if now_unitprice != i[1]:
                price = i[0] * now_unitprice
                sql = f'''
                    UPDATE '{bigname}'
                    SET price = '{price}', unitprice = '{now_unitprice}'  
                    WHERE rowid = '{i[2]}'
                '''
                cur.execute(sql)
                con.commit()

    # 零件总数
    num = sqliteObject_to_list_n(cur, f'''
        select sum(change) from "{bigname}" where smallname = '{smallname}'
    ''')
    # 零件价值
    total = float(num) * float(now_unitprice)
    # 更新
    sql2 = f'''
        UPDATE stateless_part
        SET unitprice = '{now_unitprice}', number = '{num}', total = '{total}'
        WHERE  bigname = '{bigname}' and smallname = '{smallname}'
    '''
    cur.execute(sql2)
    con.commit()


# 检查删除某行数据后是否会出现数量小于0的情况（考虑删了某行后表为零的情况）
def check_delete_not_zero(bigname, smallname, rowid):
    # 返回去掉原数据的完整记录
    con, cur = db_open()
    temp1 = sqliteObject_to_list_h(cur, f'''
            select date, change
            from '{bigname}' 
            where smallname = '{smallname}' and ROWID != '{rowid}' 
            order by date
        ''')

    # 如果这表是空的，那没事了
    if temp1 == []:
        return 3

    # 检查是否出现某时刻数量小于零的情况
    num = 0
    for i in temp1:
        num = num + i[1]
        if num < 0:
            return 1
    return 2


# 检查要增减的大类和小类是否存在
def exist(big, small):
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
                select unitprice, number, total from stateless_part where bigname = "{big}" and smallname = "{small}"
            ''')
    return temp1


# 在零件数增加后更新总表的单价，数量，订单总价
def update_add(bigname, smallname, unitprice, number, total):
    con, cur = db_open()
    sql = f'''UPDATE stateless_part SET unitprice = "{unitprice}", number = "{number}", total = "{total}"  WHERE bigname = "{bigname}" and smallname = "{smallname}"'''
    cur.execute(sql)
    con.commit()


# 在零件数减少后更新总表的单价，数量，订单总价
def update_reduce(bigname, smallname, number, total):
    con, cur = db_open()
    sql = f'''UPDATE stateless_part SET number = "{number}", total = "{total}"  WHERE bigname = "{bigname}" and smallname = "{smallname}"'''
    cur.execute(sql)
    con.commit()


# 将记录插入大类表中
def update_bigname(date, bigname, smallname, handle, change, unitprice, price):
    con, cur = db_open()
    sql = f'''
                insert into '{bigname}'(
                    date,
                    bigname,
                    smallname,
                    handle,
                    change,
                    unitprice,
                    price
                )
                values(
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?
                )
                '''
    cur.execute(sql, (date, bigname, smallname, handle, change, unitprice, price))
    con.commit()


# 更新入库记录后，保证大类表数据正常
def check_add(date, bigname, smallname, handle, change, unitprice, rowid):
    # 返回修改后记录时间之后的所有记录
    con, cur = db_open()
    temp1 = sqliteObject_to_list_h(cur, f'''
        select change, unitprice, price, rowid 
        from '{bigname}' 
        where smallname = '{smallname}' and date > '{date}' 
        order by date
    ''')
    # 现在的单价
    temp2 = unitprice
    for i in temp1:
        if i[0] > 0:
            temp2 = i[1]
        else:
            if temp2 == i[1]:
                pass
            else:
                i[1] = temp2
                i[2] = i[0] * temp2
                sql = f'''
                    UPDATE '{bigname}' 
                    SET price = '{i[2]}', unitprice = '{i[1]}'  
                    WHERE rowid = '{i[3]}'
                '''
                cur.execute(sql)
                con.commit()